<?php

App::import('Helper', 'Number'); 

Class StatsController extends AppController
{
	var $name = 'Stats';	
	var $components = array('Auth');
	var $uses = array('User');	
	
	function beforeFilter(){
		parent::beforeFilter(); 
		$this->Auth->deny('admin_demographics','admin_email','admin_psychographics','admin_song');
	}
	
	//funtion to sort multi-dimension array buy key value
	function aasort (&$array, $key) {
		$sorter=array();
		$ret=array();
		reset($array);
		foreach ($array as $ii => $va) {
			$sorter[$ii]=$va[$key];
		}
		arsort($sorter); //hight to low = arsort, lor to high = asort
		foreach ($sorter as $ii => $va) {
			$ret[$ii]=$array[$ii];
		}
		$array=$ret;
	}
	
	function admin_demographics(){
		$this->set('view','demographics');
		
		$usercount=array();
		$userdate=array();
		//$fromDate = $this->data['Stats']['from'];
		//$toDate = $this->data['Stats']['to'];
		$fromDate = '2011-06-03';
		
		if(isset($this->params['url']['from']) && isset($this->params['url']['to'])){
			$fromDate = $this->params['url']['from'];
			$toDate = $this->params['url']['to'];
		}
		else{//show current month by default
			$curr_time = time();
			//$fromDate =date('Y-m-d', strtotime('-30 day', $curr_time));
			$toDate = date('Y-m-d', $curr_time);
		}
		
		$this->data['Stats']['from'] = $fromDate;
		$this->data['Stats']['to'] = $toDate;
		$this->set('fromDate', $fromDate);
		$this->set('toDate', $toDate);
		
		//--------new user register in a date range---------------
		$sql_user="SELECT DATE(users.created) AS DATE, COUNT(1) AS user_count
					FROM users
					WHERE DATE(created) BETWEEN '".$fromDate."' AND '".$toDate."'
					GROUP BY DATE
					ORDER BY DATE ASC";
		$user_count = $this->User->query($sql_user);
		
		//count total date regardless of 0 result, initailize the array:
		$day_total = abs(strtotime($toDate) - strtotime($fromDate))/(60*60*24)+1;	
	
		
		for($i=0;$i<$day_total;$i++){
			$usercount[$i]=0;
			$add ='+'.$i.' day';
			$userdate[$i]=date('Y-m-d', strtotime($add,strtotime($fromDate)));
		}
		$max_user=0;
		foreach($user_count as $value){
			$date_index = abs(strtotime($value[0]['DATE'])-strtotime($fromDate))/(60*60*24);
			$usercount[$date_index] = $value[0]['user_count'];
			if($max_user<$value[0]['user_count'])
				$max_user=$value[0]['user_count'];
		}
		$this->set('usercount',$usercount);
		$this->set('max_user',$max_user);
		$this->set('userdate',$userdate);

		//debug($user_count);	
		//debug($usercount);
		
		//-------------gender-----------------
		$sql_gender="SELECT COUNT(1) AS gender_count, gender
					FROM
					(
					SELECT DISTINCT U.username,U.id, 
					(CASE WHEN (U.gender IS NULL) THEN 'unknown' 
					ELSE U.gender END) AS gender
					FROM users U
					WHERE DATE(U.created) BETWEEN '".$fromDate."' AND '".$toDate."'
					) Type
					GROUP BY gender";
		$gender = $this->User->query($sql_gender);
		
		//percentage:	
		$conditions1 = array ('date(created) BETWEEN ? and ?' => array($fromDate, $toDate));
		//debug($gender);
		for($i=0; $i<count($gender);$i++){
			$gender[$i]['percentage'] =  $gender[$i][0]['gender_count']/$this->User->find('count',array('conditions'=>$conditions1));
		}
		//debug($this->User->find('all',array('conditions'=>$conditions1)));
		$this->set('gender',$gender);
		//debug($gender);
		$number = new NumberHelper();
		$this->set('number',$number);
		
		//debug($gender);
		//debug($number);
		
		//------------------Age-------------------------
		$sql_age="SELECT COUNT(1) as age_count,age_range
				FROM
				(
				SELECT users.username, users.birthday, 
				(CASE
				WHEN (birthday IS NULL OR birthday < '1900/1/1') THEN 'unknown' 
				WHEN (YEAR(NOW())- YEAR(users.birthday)) <= 15 THEN 'age 15 and below' 
				WHEN (YEAR(NOW())- YEAR(users.birthday)) <= 25 THEN 'age 16 - 25' 
				WHEN (YEAR(NOW())- YEAR(users.birthday)) <= 35 THEN 'age 26 - 35' 
				WHEN (YEAR(NOW())- YEAR(users.birthday)) <= 50 THEN 'age 36 - 50' 
				WHEN (YEAR(NOW())- YEAR(users.birthday)) > 50 THEN 'age 50 above' 

				END) AS age_range
				FROM users 
				where DATE(users.created) BETWEEN '".$fromDate."' AND '".$toDate."'
				) Age
				GROUP BY age_range";
		$age =$this->User->query($sql_age);
		//debug($age);
		//percentage
		for($i=0; $i<count($age);$i++){
			$age[$i]['percentage'] =  $age[$i][0]['age_count']/$this->User->find('count',array('conditions'=>$conditions1));
		}
		$this->set('age',$age);
		//debug($gender);
		
		//-----------------------location-------------------------
		$sql_location="SELECT COUNT(1) as location_count, location
						FROM
						(
						SELECT DISTINCT U.id, 
						(CASE WHEN (U.location IS NULL) THEN 'Other' ELSE
						 L.name END) AS location
						FROM users U, locations L
						WHERE (U.location = L.id OR U.location IS NULL)
						AND (DATE(U.created) BETWEEN '".$fromDate."' AND '".$toDate."')
						) Geo
						GROUP BY location";
		$location = $this->User->query($sql_location);
		//debug($location);
		$max_geo=0;
		$locationgeo=array();
		$locationcount=array();
		$j=0;
		foreach($location as $value){
		
			$locationgeo[$j] = $value['Geo']['location'];
			$locationcount[$j] = $value[0]['location_count'];
			if($max_geo<$value[0]['location_count'])
				$max_geo=$value[0]['location_count'];
			$j++;
		}
		//debug($location);
		//debug($max_geo);
		$this->set('max_geo',$max_geo);
		$this->set('locationgeo',$locationgeo);
		$this->set('locationcount',$locationcount);
		
		foreach($gender as $gen)
		{
			//debug($gen['Type']['gender']);
			$result_details = $this->User->gender_detail($gen['Type']['gender'], $fromDate, $toDate);
			$this->set('user_'.$gen['Type']['gender'].'s', $result_details);	
		}
		
/* 		foreach($age as $age_range)
		{
			debug(str_replace(" ", "_", $age_range['Age']['age_range']));
			// $result_details = $this->User->age_detail($age_range['Age']['age_range'], $fromDate, $toDate);
			// $this->set('user_'.$gen['Type']['gender'].'s', $result_details);	
		} */
		//15 and below
		$user_15 = $this->User->age_detail('15 and below', $fromDate, $toDate);
		$this->set('user_15', $user_15);

		//16 - 25
		$user_1625 = $this->User->age_detail('16 - 25', $fromDate, $toDate);
		$this->set('user_1625', $user_1625);
		
		//26 - 35
		$user_2635 = $this->User->age_detail('26 - 35', $fromDate, $toDate);
		$this->set('user_2635', $user_2635);
		
		//36 - 50
		$user_3650 = $this->User->age_detail('36 - 50', $fromDate, $toDate);
		$this->set('user_3650', $user_3650);
		
		//50 above
		$user_50 = $this->User->age_detail('50 above', $fromDate, $toDate);
		$this->set('user_50', $user_50);
		
		//unknown
		$user_age_unknown = $this->User->age_detail('unknown', $fromDate, $toDate);
		$this->set('user_age_unknown', $user_age_unknown);
		
	}//end of admin_demographics
	
	function  admin_psychographics(){
		$this->set('view','psychographics');
		
		$user_ids = $this->User->find('all', array('fields'=>array('User.id', 'User.username', 'User.name')));
	
		$number = new NumberHelper();
		$this->set('number',$number);
		
		$fromDate = '2011-06-03';
		
		if(isset($this->params['url']['from']) && isset($this->params['url']['to'])){
			$fromDate = $this->params['url']['from'];
			$toDate = $this->params['url']['to'];
		}
		else{//show current month by default
			$curr_time = time();
			//$fromDate =date('Y-m-d', strtotime('-30 day', $curr_time));
			$toDate = date('Y-m-d', $curr_time);	
		}
		
		$this->data['Stats']['from'] = $fromDate;
		$this->data['Stats']['to'] = $toDate;
		
		//------------------------loyal-------------------------------
		$sql_loyal="SELECT U.name, L.user_id, count(1) AS loyal_count
					FROM loginlogs L, users U
					WHERE DATE(L.created) BETWEEN '".$fromDate."' AND '".$toDate."' AND L.user_id = U.id
					GROUP BY L.user_id";			
		$loyal = $this->Loginlog->query($sql_loyal);
		$loyal_all=array();
		$total_score1=0;
		//debug($loyal);
		foreach($user_ids as $user_id){
			$loyal_count = 0;
			foreach($loyal as $value){
				if($value['L']['user_id']==$user_id['User']['id']){
					$loyal_count =$value[0]['loyal_count'];
					break;
				}
			}
			
			$loyal_all[]=array('id'=>$user_id['User']['id'], 
								'name'=>$user_id['User']['name'],
								'username'=>$user_id['User']['username'],
								'loyal_count'=>$loyal_count);
			
			$total_score1 += $loyal_count;		
		}
		
		//debug($loyal);
		//debug($loyal_all);
		$average_loyal = $total_score1/count($loyal_all);
		$L_group1 = array();
		$L_group2 = array();
		$L_group3 = array();
		$L_group4 = array();
		$L_group5 = array();
		
		$loyal_p = array(
								'Very loyal'=>0, 
								'Loyal'=>0, 
								'Average'=>0, 
								'Occasionally'=>0, 
								'Rarely'=>0);
								
		foreach($loyal_all as $value){
			if($value['loyal_count']>=$average_loyal*4/2.5){ 
				$loyal_p['Very loyal']+=1/count($loyal_all);
				array_push($L_group1, $value); 
			}
			else if($value['loyal_count']>=$average_loyal*3/2.5){ 
				$loyal_p['Loyal']+=1/count($loyal_all);
				array_push($L_group2, $value); 
			}
			else if($value['loyal_count']>=$average_loyal*2/2.5){ 
				$loyal_p['Average']+=1/count($loyal_all);
				array_push($L_group3, $value); 
			}
			else if($value['loyal_count']>=$average_loyal/2.5){ 
				$loyal_p['Occasionally']+=1/count($loyal_all);
				array_push($L_group4, $value); 
			}
			else{
				$loyal_p['Rarely']+=1/count($loyal_all);
				array_push($L_group5, $value); 
			}
		}  
		//debug($loyal);
		$this->aasort($L_group1,"loyal_count");
		$this->aasort($L_group2,"loyal_count");
		$this->aasort($L_group3,"loyal_count");
		$this->aasort($L_group4,"loyal_count");
		$this->aasort($L_group5,"loyal_count");
		
		$sliced_L_group1 = array_slice($L_group1, 0, 20);
		$sliced_L_group2 = array_slice($L_group2, 0, 20);
		$sliced_L_group3 = array_slice($L_group3, 0, 20);
		$sliced_L_group4 = array_slice($L_group4, 0, 20);
		$sliced_L_group5 = array_slice($L_group5, 0, 20);
		
		$this->set('loyal_p',$loyal_p);
		$this->set('L_group1', $sliced_L_group1);
		$this->set('L_group2', $sliced_L_group2);
		$this->set('L_group3', $sliced_L_group3);
		$this->set('L_group4', $sliced_L_group4);
		$this->set('L_group5', $sliced_L_group5);
		
		//-------------------------influential------------------------------
		$email = "SELECT E.user_id, COUNT(1) AS email_count
				FROM
				emailshares E
				WHERE DATE(E.created) BETWEEN '".$fromDate."' AND '".$toDate."'
				GROUP BY E.user_id";
		$fb = "SELECT SS.user_uid, SS.user_id, COUNT(1) AS fb_count
				FROM songshares SS, users U
				WHERE SS.user_uid=U.uid AND DATE(SS.created) BETWEEN '".$fromDate."' AND '".$toDate."'
				GROUP BY SS.user_uid";
				
		$sql_influential = "SELECT Email.user_id as e_id, Email.email_count, FB.user_id as f_id, FB.user_uid, FB.fb_count
						FROM(".$email.") Email
						LEFT JOIN
						(".$fb.") FB ON Email.user_id=FB.user_id 
						UNION
						SELECT Email.user_id as e_id, Email.email_count, FB.user_id as f_id, FB.user_uid, FB.fb_count
						FROM(".$email.") Email
						RIGHT JOIN
						(".$fb.") FB ON Email.user_id=FB.user_id";
		$influential = $this->Emailshare->query($sql_influential);
			
		//compute score=emailsent + fbshare*1.2
		$total_score=0;
		$influential_all=array();
		//debug($influential);
		foreach($user_ids as $user_id){
			$score=0;
			
			for($i=0; $i<count($influential);$i++){
				if($influential[$i][0]['e_id']==$user_id['User']['id']||$influential[$i][0]['f_id']==$user_id['User']['id']){
					if(empty($influential[$i][0]['email_count'])) {
						$influential[$i][0]['email_count']=0;
						//$influential[$i][0]['username']=$this->User->findById($influential[$i][0]['f_id'], array('fields'=>'User.username'));
					}
					if(empty($influential[$i][0]['fb_count'])) {
						$influential[$i][0]['fb_count']=0;
						//$influential[$i][0]['username']=$this->User->findById($influential[$i][0]['e_id'], array('fields'=>'User.username'));	
					}
					
					$score = $influential[$i][0]['email_count']+$influential[$i][0]['fb_count']*1.2;
					break;
				}
				
				
			}
			
			$influential_all[] = array('id'=>$user_id['User']['id'], 
								'name'=>$user_id['User']['name'],
								'username'=>$user_id['User']['username'],
								'score'=>$score);
								
			$total_score += $score;
		}
		//debug($influential_all);
		//categories
		$average_influential=$total_score/count($influential_all);
		$I_group1 = array();
		$I_group2 = array();
		$I_group3 = array();
		$I_group4 = array();
		$I_group5 = array();
		$influential_p = array(
								'Very Influential'=>0, 
								'Significant'=>0, 
								'Average'=>0, 
								'Mildly Significant'=>0, 
								'Insignificant'=>0);
								
		foreach($influential_all as $value){
			if($value['score']>=$average_influential*4/2.5){ 
				$influential_p['Very Influential']+=1/count($influential_all);
				array_push($I_group1, $value); 
			}
			else if($value['score']>=$average_influential*3/2.5){ 
				$influential_p['Significant']+=1/count($influential_all);
				array_push($I_group2, $value); 
			}
			else if($value['score']>=$average_influential*2/2.5){			
				$influential_p['Average']+=1/count($influential_all);
				array_push($I_group3, $value); 
			}
			else if($value['score']>=$average_influential/2.5){ 
				$influential_p['Mildly Significant']+=1/count($influential_all);
				array_push($I_group4, $value); 
			}	
			else{
				$influential_p['Insignificant']+=1/count($influential_all);
				array_push($I_group5, $value); 
			}
		}
		//debug($influential_p);
		
		$this->aasort($I_group1,"score");
		$this->aasort($I_group2,"score");
		$this->aasort($I_group3,"score");
		$this->aasort($I_group4,"score");
		$this->aasort($I_group5,"score");
		
		$sliced_I_group1 = array_slice($I_group1, 0, 20);
		$sliced_I_group2 = array_slice($I_group2, 0, 20);
		$sliced_I_group3 = array_slice($I_group3, 0, 20);
		$sliced_I_group4 = array_slice($I_group4, 0, 20);
		$sliced_I_group5 = array_slice($I_group5, 0, 20);
		
		$this->set('influential_p',$influential_p);
		$this->set('I_group1', $sliced_I_group1);
		$this->set('I_group2', $sliced_I_group2);
		$this->set('I_group3', $sliced_I_group3);
		$this->set('I_group4', $sliced_I_group4);
		$this->set('I_group5', $sliced_I_group5);
		
		//debug($sliced_I_group1);
		
		//-------------------------active user------------------------------download+play+facebook_share+email_share+pageview
		$date1 = $fromDate;
		$date2 = $toDate;
		
		$sql_fb_song_share = "select U.id, count(1) as fb_song_share from users U, songshares SS where SS.user_id = U.id and DATE(U.created) BETWEEN '".$date1."' AND '".$date2."' group by U.name";
		$sql_email_song_share = "select U.id, count(1) as email_song_share from users U, emailshares ES where ES.user_id = U.id and DATE(U.created) BETWEEN '".$date1."' AND '".$date2."' group by U.name";
		$sql_song_play = "select U.id, count(1) as song_play from users U, songplays SP where SP.user_id = U.id and DATE(U.created) BETWEEN '".$date1."' AND '".$date2."' group by U.name";
		$sql_song_download = "select U.id, count(1) as song_download from users U, songdownloads SD where SD.user_id = U.id and DATE(U.created) BETWEEN '".$date1."' AND '".$date2."' group by U.name";
		$sql_page_view = "select U.id, count(1) as page_view from users U, pageviews PV where PV.user_id = U.id and DATE(U.created) BETWEEN '".$date1."' AND '".$date2."' group by U.name";
		$sql_unique_login = "SELECT L.user_id, COUNT(1) AS loyal_count
								FROM loginlogs L where DATE(L.created) BETWEEN '".$date1."' AND '".$date2."'
								GROUP BY L.user_id";
								
		
		$fb_song_shares = $this->Songshare->query($sql_fb_song_share);
		$email_song_shares = $this->Emailshare->query($sql_email_song_share);
		$song_plays = $this->Songplay->query($sql_song_play);
		$song_downloads = $this->Songdownload->query($sql_song_download);
		$page_views = $this->Pageview->query($sql_page_view);
		$unique_logins = $this->Loginlog->query($sql_unique_login);
		
		$users = array();
		$total_score_active = 0;
		
		$weight1 = 0.3;
		$weight2 = 0.25;
		$weight3 = 0.2;
		$weight4 = 0.15;
		$weight5 = 0.08;
		$weight6 = 0.02;
		
		//$total_score_active=0;
		
  		foreach($user_ids as &$user_id)
		{
			$temp1 = 0;
			$temp2 = 0;
			$temp3 = 0;
			$temp4 = 0;
			$temp5 = 0;
			$temp6 = 0;
			
 			foreach($fb_song_shares as $fb_song_share)
			{
				if($user_id['User']['id'] == $fb_song_share['U']['id'])
				{
					//debug($user_id['User']['id']);
					$temp1 = $fb_song_share[0]['fb_song_share'];
					//$users[] = array('User'=>array('id'=>$fb_song_share['U']['id'], 'fb_song_share'=>$fb_song_share[0]['fb_song_share']));
					//$users['User']['id'] = $fb_song_share['U']['id'];
					//$users['User']['fb_song_share'] = $fb_song_share[0]['fb_song_share'];
					break;
				}
			} 
			
 			foreach($email_song_shares as $email_song_share)
			{
				if($user_id['User']['id'] == $email_song_share['U']['id'])
				{
					//debug($email_song_share[0]['email_song_share']);
					$temp2 = $email_song_share[0]['email_song_share'];
					//$users[] = array('User'=>array('id'=>$fb_song_share['U']['id'], 'fb_song_share'=>$fb_song_share[0]['fb_song_share']));
					//$users['User']['id'] = $fb_song_share['U']['id'];
					//$users['User']['fb_song_share'] = $fb_song_share[0]['fb_song_share'];
					break;
				}
			} 
			
 			foreach($song_plays as $song_play)
			{
				if($user_id['User']['id'] == $song_play['U']['id'])
				{
					//debug($song_play);
					//debug($song_play[0]['song_play']);
					$temp3 = $song_play[0]['song_play'];

					break;
				}
			} 
			
			
 			foreach($song_downloads as $song_download)
			{
				if($user_id['User']['id'] == $song_download['U']['id'])
				{
					// debug($song_download);
					// debug($song_download[0]['song_download']);
					$temp4 = $song_download[0]['song_download'];

					break;
				}
			} 
			
 			foreach($page_views as $page_view)
			{
				if($user_id['User']['id'] == $page_view['U']['id'])
				{
					//debug($page_view);
					//debug($page_view[0]['page_view']);
					$temp5 = $page_view[0]['page_view'];

					break;
				}
			} 
			
			foreach($unique_logins as $unique_login)
			{

		 		if($user_id['User']['id'] == $unique_login['L']['user_id'])
				{
					// debug($unique_login);
					// debug($unique_login[0]['loyal_count']);
					$temp6 = $unique_login[0]['loyal_count'];

					break;
				} 
			} 

			
			$users[] = array('id'=>$user_id['User']['id'], 
										  'name'=>$user_id['User']['name'], 
										  'username'=>$user_id['User']['username'], 
										  'fb_song_share'=>$temp1*$weight1, 
										  'email_song_share'=>$temp2*$weight2,
										  'song_play'=>$temp3*$weight3,
										  'song_download'=>$temp4*$weight4,
										  'page_view'=>$temp5*$weight5,
										  'unique_login'=>$temp6*$weight6,
										  'total'=>$temp1*$weight1+$temp2*$weight2+$temp3*$weight3+$temp4*$weight4+$temp5*$weight5+$temp6*$weight6,
										  );

			$total_score_active += $temp1*$weight1+$temp2*$weight2+$temp3*$weight3+$temp4*$weight4+$temp5*$weight5+$temp6*$weight6;

		}  

		$average_active = $total_score_active/count($users);
		//debug($users);
		$A_group1 = array();
		$A_group2 = array();
		$A_group3 = array();
		$A_group4 = array();
	
		$active_p = array(
								'Very active'=>0, 
								'Average'=>0, 
								'Rarely active'=>0, 
								'Inactive'=>0);		
						
		foreach($users as $user){
						//debug($user['User']['total']);
		
			if($user['total']>=$average_active*3/2){ 
				$active_p['Very active']+=1/count($users);
				array_push($A_group1, $user);
				
			}
			else if($user['total']>=$average_active) {
				$active_p['Average']+=1/count($users);
				array_push($A_group2, $user);
				
			}
			else if($user['total']>=$average_active/2) {
				$active_p['Rarely active']+=1/count($users);
				array_push($A_group3, $user);
			}
			else{
				$active_p['Inactive']+=1/count($users);
				array_push($A_group4, $user);
			}
		}
		
		$this->aasort($A_group1,"total");
		$this->aasort($A_group2,"total");
		$this->aasort($A_group3,"total");
		$this->aasort($A_group4,"total");
		
		$sliced_A_group1 = array_slice($A_group1, 0, 20);
		$sliced_A_group2 = array_slice($A_group2, 0, 20);
		$sliced_A_group3 = array_slice($A_group3, 0, 20);
		$sliced_A_group4 = array_slice($A_group4, 0, 20);
		
		$this->set('active_p', $active_p);
		$this->set('A_group1', $sliced_A_group1);
		$this->set('A_group2', $sliced_A_group2);
		$this->set('A_group3', $sliced_A_group3);
		$this->set('A_group4', $sliced_A_group4);
		
		
		//debug($A_group1);
		//debug($active_p);
		//debug($total_score_active);
		
	}//end of adming_phychographics
	

	


	function admin_email(){
		$this->set('view','email');
		//-------------email count-------------
		$sentcount=array();
		$opencount=array();
		$clickcount=array();
		$emaildate = array();
		
		$fromDate = '2011-06-03';
		
		if(isset($this->params['url']['from']) && isset($this->params['url']['to'])){
			$fromDate = $this->params['url']['from'];
			$toDate = $this->params['url']['to'];
		}
		else{//show current month by default
			$curr_time = time();
			//$fromDate =date('Y-m-d', strtotime('-30 day', $curr_time));
			$toDate = date('Y-m-d', $curr_time);	
		}
		
		$this->data['Stats']['from'] = $fromDate;
		$this->data['Stats']['to'] = $toDate;
		
		$sql_sent ="SELECT DATE(created) AS DATE, COUNT(1) AS sent_count
				FROM emailshares
				WHERE DATE(created) BETWEEN '".$fromDate."' AND '".$toDate."'
				GROUP BY DATE(created)
				ORDER BY DATE ASC";

		$emailsent = $this->Emailshare->query($sql_sent);
		
		$sql_open ="SELECT DATE(created) AS DATE, COUNT(DISTINCT email_id) AS open_count
				FROM emailopens
				WHERE DATE(created) BETWEEN '".$fromDate."' AND '".$toDate."'
				GROUP BY DATE(created)
				ORDER BY DATE ASC";

		$emailopen = $this->Emailopen->query($sql_open);
		
		$sql_click ="SELECT DATE(created) AS DATE, COUNT(1) AS click_count
				FROM emailclicks
				WHERE DATE(created) BETWEEN '".$fromDate."' AND '".$toDate."'
				GROUP BY DATE(created)
				ORDER BY DATE ASC";

		$emailclick = $this->Emailclick->query($sql_click);
		
		//debug($emailsent);
		
		//count total date regardless of 0 result, initailize the array:
		$day_total = abs(strtotime($toDate) - strtotime($fromDate))/(60*60*24)+1;	
		$max=0;
		for($i=0;$i<$day_total;$i++){
			$sentcount[$i]=0;
			$opencount[$i]=0;
			$clickcount[$i]=0;
			$add ='+'.$i.' day';
			$emaildate[$i]=date('Y-m-d', strtotime($add,strtotime($fromDate)));
		}
		$this->set('emaildate',$emaildate);
		$this->set('day_total',$day_total);
		
		foreach($emailsent as $value){
			$date_index = abs(strtotime($value[0]['DATE'])-strtotime($fromDate))/(60*60*24);
			$sentcount[$date_index] = $value[0]['sent_count'];
			if($max<$value[0]['sent_count'])
				$max=$value[0]['sent_count'];
		}
		$this->set('sentcount',$sentcount);
		foreach($emailopen as $value){
			$date_index = abs(strtotime($value[0]['DATE'])-strtotime($fromDate))/(60*60*24);
			$opencount[$date_index] = $value[0]['open_count'];
			if($max<$value[0]['open_count'])
				$max=$value[0]['open_count'];			
		}
		$this->set('opencount',$opencount);
		foreach($emailclick as $value){
			$date_index = abs(strtotime($value[0]['DATE'])-strtotime($fromDate))/(60*60*24);
			$clickcount[$date_index] = $value[0]['click_count'];
			if($max<$value[0]['click_count'])
				$max=$value[0]['click_count'];
		}
		$this->set('clickcount',$clickcount);
		$this->set('max',$max);
		
		//--------------percentage-------------------
		$number = new NumberHelper();
		$this->set('number',$number);	
		$conditions1 = array ("date(Emailshare.created) >=" => $fromDate, "Emailshare.created <=" => $toDate);
		$conditions2 = array ("date(Emailopen.created) >=" => $fromDate, "Emailopen.created <=" => $toDate);
		$conditions3 = array ("date(Emailclick.created) >=" => $fromDate, "Emailclick.created <=" => $toDate);
		$totalsent = $this->Emailshare->find('count',array('conditions'=>$conditions1));
		$totalopen = $this->Emailopen->find('count', array('fields' => 'DISTINCT Emailopen.email_id', 'conditions'=>$conditions2));
		$totalclick = $this->Emailclick->find('count', array('fields' => 'DISTINCT Emailclick.email_id', 'conditions'=>$conditions3));
		//$totalclick = $this->Emailshare->find('count');
		if($totalsent-$totalopen>=0){
			$opennot=array('Email opened'=>$totalopen,
								'Email has not been opened'=>($totalsent-$totalopen));
		}
		else{
			$opennot=array('Email opened'=>$totalopen,
								'Email has not been opened'=>0);
		}
		if($totalsent!=0){
			$open_per = $totalopen/$totalsent;
			$click_per = $totalclick/$totalsent;
		}
		else{
			$open_per = 0;
			$click_per = 0;
		}
		$this->set('open_per',$open_per);
		$this->set('click_per',$click_per);
		$this->set('opennot', $opennot);
		
		//--------------top sender-------------------
		$sql_topsender="SELECT COUNT(1) AS total_sent, sender_email, user_id
						FROM emailshares
						WHERE DATE(created) BETWEEN '".$fromDate."' AND '".$toDate."'
						GROUP BY sender_email
						ORDER BY total_sent DESC";
		$topsender = $this->Emailshare->query($sql_topsender);
		$this->set('topsender',$topsender);
		//debug($topsender);
		
		//--------------top influencer-------------------
		$sql_topinfluencer="SELECT COUNT(emailclicks.email_id) AS total_click, emailshares.sender_email, emailshares.user_id
						FROM emailshares
						JOIN emailclicks
						ON emailclicks.email_id=emailshares.id
						WHERE DATE(emailclicks.created) BETWEEN '".$fromDate."' AND '".$toDate."'
						GROUP BY emailshares.sender_email
						ORDER BY total_click DESC";
		$topinfluencer = $this->Emailshare->query($sql_topinfluencer);
		$this->set('topinfluencer',$topinfluencer);
		//debug($topinfluencer);
		
	}//end of admin_email function
	
	
	
	
	function admin_song(){
		$this->set('view','song');
		//initialize:
		$songcount =array();
		for($i=0;$i<5;$i++){
			$songcount[$i]['song_id']=$i+1;
			$song=$this->Song->findById($i+1);
			$songcount[$i]['song_name']=$song['Song']['title'];
			$songcount[$i]['play_count']=0;
			$songcount[$i]['download_count']=0;
			$songcount[$i]['fbshare']=0;
			$songcount[$i]['emailshare']=0;
		}

		$fromDate = '2011-06-03';
		
		if(isset($this->params['url']['from']) && isset($this->params['url']['to'])){
			$fromDate = $this->params['url']['from'];
			$toDate = $this->params['url']['to'];
		}
		else{//show current month by default
			$curr_time = time();
			//$fromDate =date('Y-m-d', strtotime('-30 day', $curr_time));
			$toDate = date('Y-m-d', $curr_time);
		}
		
		$this->data['Stats']['from'] = $fromDate;
		$this->data['Stats']['to'] = $toDate;
		
		//----------------play count------------
		$sql_playcount="SELECT song_id, COUNT(1) AS play_count
						FROM songplays
						WHERE DATE(created) BETWEEN '".$fromDate."' AND '".$toDate."'
						GROUP BY song_id
						ORDER BY song_id ASC";
		$playcount = $this->Songplay->query($sql_playcount);
		foreach($playcount as $value){
			$index = $value['songplays']['song_id'];
			if($index>=1)
			$songcount[$index-1]['play_count'] = $value[0]['play_count'];
		}
		
		//-----------download count------------
		$sql_downloadcount="SELECT song_id, COUNT(1) AS download_count
							FROM songdownloads
							WHERE DATE(created) BETWEEN '".$fromDate."' AND '".$toDate."'
							GROUP BY song_id
							ORDER BY song_id ASC";
		$downloadcount = $this->Songdownload->query($sql_downloadcount);
		foreach($downloadcount as $value){
			$index = $value['songdownloads']['song_id'];
			if($index>=1)
			$songcount[$index-1]['download_count'] = $value[0]['download_count'];
		}
		
		//-----------facebook share-----------
		$sql_fbshare="SELECT song_id, COUNT(1) AS fbshare_count
							FROM songshares
							WHERE DATE(created) BETWEEN '".$fromDate."' AND '".$toDate."'
							GROUP BY song_id
							ORDER BY song_id ASC";
		$fbshare = $this->Songshare->query($sql_fbshare);
		foreach($fbshare as $value){
			$index = $value['songshares']['song_id'];
			if($index>=1)
			$songcount[$index-1]['fbshare'] = $value[0]['fbshare_count'];
		}
		
		//---------email share---------------
		$sql_emailshare="SELECT song_id, COUNT(1) AS emailshare_count
						FROM emailshares
						WHERE DATE(created) BETWEEN '".$fromDate."' AND '".$toDate."'
						GROUP BY song_id
						ORDER BY song_id ASC";
		$emailshare = $this->Emailshare->query($sql_emailshare);
		foreach($emailshare as $value){
			$index = $value['emailshares']['song_id'];
			if($index>=1)
			$songcount[$index-1]['emailshare'] = $value[0]['emailshare_count'];
		}
		//debug($fbshare);
		//debug($songcount);
		$this->set('songcount',$songcount);
	}//end of admin_song
  
  
  
	function admin_detail($category, $sub_category, $fromDate, $toDate){
		//$tc = $this->Term->find('all');
		$category = "gender";
		
		switch($category)
		{
			case "gender":
				$sql = "select * from (SELECT DISTINCT U.username,
						(CASE WHEN (U.gender IS NULL) THEN 'unknown' 
						ELSE U.gender END) AS gender1
						FROM users U
						WHERE DATE(U.created) BETWEEN '2011-04-01' AND '2011-06-09') AA where AA.gender1 = 'male' LIMIT 20 ";
				
				$user_genders = $this->User->query($sql);
				$this->set('user_genders', $user_genders);
				//debug($user_genders);
				break;
			case "age":
				$sql = "select * from (SELECT users.username, 
						(CASE
						WHEN (YEAR(NOW())- YEAR(users.birthday)) <= 15 THEN '15 and below' 
						WHEN (YEAR(NOW())- YEAR(users.birthday)) <= 25 THEN '16 - 25' 
						WHEN (YEAR(NOW())- YEAR(users.birthday)) <= 35 THEN '26 - 35' 
						WHEN (YEAR(NOW())- YEAR(users.birthday)) <= 50 THEN '36 - 50' 
						WHEN (YEAR(NOW())- YEAR(users.birthday)) > 50 THEN '50 above' 
						WHEN (birthday IS NULL OR birthday < '1900/1/1') THEN 'unknown' 
						END) AS age_range
						FROM users 
						where DATE(users.created) BETWEEN '2011-04-01' AND '2011-06-09') AA WHERE AA.age_range = '15 and below'";
				
				$user_genders = $this->User->query($sql);
				$this->set('user_genders', $user_genders);
				//debug($user_genders);
				break;
			case "loyal":
				$sql = "SELECT U.username, count(1) AS loyal_count
						FROM loginlogs L, users U
						WHERE DATE(L.created) BETWEEN '2011-04-01' AND '2011-06-09' AND L.user_id = U.id
						GROUP BY L.user_id ORDER BY loyal_count DESC LIMIT 20";
				
				$user_genders = $this->User->query($sql);
				$this->set('user_genders', $user_genders);
				//debug($user_genders);
				break;
			case "active":
				$sql = "SELECT DISTINCT U.username,
						(CASE WHEN (U.gender IS NULL) THEN 'unknown' 
						ELSE U.gender END) AS gender
						FROM users U
						WHERE DATE(U.created) BETWEEN '2011-04-01' AND '2011-06-09' AND gender = 'male'";
				
				$user_genders = $this->User->query($sql);
				$this->set('user_genders', $user_genders);
				//debug($user_genders);
				break;
			case "influential":
				$sql = "SELECT DISTINCT U.username,
						(CASE WHEN (U.gender IS NULL) THEN 'unknown' 
						ELSE U.gender END) AS gender
						FROM users U
						WHERE DATE(U.created) BETWEEN '2011-04-01' AND '2011-06-09' AND gender = 'male'";
				
				$user_genders = $this->User->query($sql);
				$this->set('user_genders', $user_genders);
				//debug($user_genders);
				break;
			default:
				break;
		}
		$this->set('termsconditions', 'testing data');
		$this->layout = null;
	}
  
}

?>